<?php
class Report_model extends MY_Model {
	
    function Report_model(){
        parent::MY_Model();
        $this->load->database();
    }
    
    /**
     * total poin karyawan per periode
     * @param $karyID
     * @param $periodeStart
     * @param $periodeEnd
     */
    function totalPointKaryawan($karyID, $periodeStart, $periodeEnd){
    	$start = date("Y-m",strtotime($periodeStart));
		$end   = date("Y-m",strtotime($periodeEnd));
		$start = explode('-',$start);
		$end = explode('-',$end);
		$startYear = $start[0];
		$startMonth = $start[1];
		$endYear = $end[0];
		$endMonth = $end[1];
		$data = array();
		
		//flag untuk perbuatahan tahun. jika tahun berubah patokan bulan tidak lagi $startMonth
		$sm=$se=$yearChange=0;
		for($year=$startYear; $year<=$endYear; $year++){
			if ($startYear == $endYear){
				$sm = $startMonth;
				$se = $endMonth;
				$yearChange = false;
			}
			else{
				//jika tahun berubah, set bulan menjadi bulan pertama
				$sm = $yearChange? 1 : $startMonth;
				$se = 12;
				$yearChange = true;
			}
			
			$data = array_merge($data,  $this->_totalPointKaryawan($karyID, $sm, $se, $year) );
		}
		
		return $data;
    }
    
	function _totalPointKaryawan($karyID, $startMonth, $endMonth, $year){
		$data = array();
		for($month=$startMonth; $month<=$endMonth; $month++){
			$result = $this->db->get_where('ranking',"kary_id='$karyID' AND year(rank_periode)='$year' AND month(rank_periode)='$month'");
			$tmp = (array)$result->row();
			$data["$month-$year"] = isset($tmp['rank_point'])? $tmp['rank_point'] : 0;	
		}
		return $data;
	}
	
	/**
	 * rata-rata poin seluruh departemen per periode
	 * F = ( rata2PoinDepartemen() untuk tiap departemen  / jumlah departemen)
	 * 
	 * @param $departemenID
	 * @param $periodeStart
	 * @param $periodeEnd
	 */
	function rata2PoinSeluruhDepartemen($periodeStart, $periodeEnd){
		$start = date("Y-m",strtotime($periodeStart));
		$end   = date("Y-m",strtotime($periodeEnd));
		$start = explode('-',$start);
		$end = explode('-',$end);
		$startYear = $start[0];
		$startMonth = $start[1];
		$endYear = $end[0];
		$endMonth = $end[1];
		$data = array();
		
		//flag untuk perbuatahan tahun. jika tahun berubah patokan bulan tidak lagi $startMonth
		$sm=$se=$yearChange=0;
		for($year=$startYear; $year<=$endYear; $year++){	
			if ($startYear == $endYear){
				$sm = $startMonth;
				$se = $endMonth;
				$yearChange = false;
			}
			else{
				//jika tahun berubah, set bulan menjadi bulan pertama
				$sm = $yearChange? 1 : $startMonth;
				$se = 12;
				$yearChange = true;
			}
			$data = array_merge($data,  $this->_rata2PoinSeluruhDepartemen($sm, $se, $year) );
		}
		
		return $data;
	}
	
	function _rata2PoinSeluruhDepartemen($startMonth, $endMonth, $year){
		$data = array();
		
		//kita load seluruh departemen
		$depData = $this->db->get('departemen');
		$depCount = 0;
		$tmp = array();
		foreach($depData->result_array() as $row){
			//kita load rata-rata point tiap departemen
			$tmp[$row['dep_id']] = $this->_rata2PoinDepartemen($row['dep_id'],$startMonth, $endMonth, $year);
			$depCount++;
		}
		
		for($month=$startMonth; $month<=$endMonth; $month++){
			$data["$month-$year"] = 0;
			
			foreach($depData->result_array() as $row){
				if (!empty($tmp[$row['dep_id']]["$month-$year"])){
					$data["$month-$year"] += $tmp[$row['dep_id']]["$month-$year"];
				}
			}
			
			if (!empty($data["$month-$year"])){
				$data["$month-$year"] = $data["$month-$year"]/$depCount;
			}
		}
		
		return $data;
	}
	
	/**
	 * rata-rata poin departemen per periode
	 * F = (total point departemen / jumlah kejadian)
	 *  
	 * @param $departemenID
	 * @param $periodeStart
	 * @param $periodeEnd
	 */
	function rata2PoinDepartemen($departemenID, $periodeStart, $periodeEnd){
		$start = date("Y-m",strtotime($periodeStart));
		$end   = date("Y-m",strtotime($periodeEnd));
		$start = explode('-',$start);
		$end = explode('-',$end);
		$startYear = $start[0];
		$startMonth = $start[1];
		$endYear = $end[0];
		$endMonth = $end[1];
		$data = array();
		
		//flag untuk perbuatahan tahun. jika tahun berubah patokan bulan tidak lagi $startMonth
		$sm=$se=$yearChange=0;
		for($year=$startYear; $year<=$endYear; $year++){
			if ($startYear == $endYear){
				$sm = $startMonth;
				$se = $endMonth;
				$yearChange = false;
			}
			else{
				//jika tahun berubah, set bulan menjadi bulan pertama
				$sm = $yearChange? 1 : $startMonth;
				$se = 12;
				$yearChange = true;
			}
			$data = array_merge($data,  $this->_rata2PoinDepartemen($departemenID, $sm, $se, $year) );
		}
		
		return $data;
	}
	
	function _rata2PoinDepartemen($departemenID, $startMonth, $endMonth, $year){
		//kumpulkan jabatan yang berada dalam departemen $departemenID
		$jbtData = $this->db->get_where('jabatan',"dep_id='$departemenID'");
		$jbtList = "";
		foreach ($jbtData->result_array() as $jbt){
			$jbtList .= ($jbtList? "," : "")."'$jbt[jbt_id]'";
		}
		
		//kia cari dalam tabel karyawan_history_jabatan
		$karyJbtData = $this->db->get_where('karyawan_history_jabatan',"jbt_id IN ($jbtList)");
		$karyJbtList = "";
		foreach ($karyJbtData->result_array() as $karyJbt){
			$karyJbtList .= ($karyJbtList? "," : "")."'$karyJbt[kary_jbt_id]'";
		}
			
		$data = array();
		for($month=$startMonth; $month<=$endMonth; $month++){
			//kita sudah dapatkan id history jabatan karyawan,
			//cari dalam tabel penilaian yang memiliki id history jabatan tersebut
			$penilaianData = $this->db->get_where('penilaian',"kary_jbt_id IN ($karyJbtList) ".
				"AND year(point_tgl_aktif)='$year' AND month(point_tgl_aktif)='$month'");
			
			$devider = 0;
			$point = 0;
			foreach($penilaianData->result_array() as $row){
				$point += $row['point_point'];
				$devider++;				
			}
			
			if ($devider>0) $point = $point/$devider; 
			
			$data["$month-$year"] = $point;
		}
		return $data;
	} 

	/**
	 * rata-rata poin keseluruhan
	 * F = (total tiap poin / jumlah kejadian)
	 * @param unknown_type $periodeStart
	 * @param unknown_type $periodeEnd
	 */
	function rata2SeluruhPoin($periodeStart, $periodeEnd){
		$start = date("Y-m",strtotime($periodeStart));
		$end   = date("Y-m",strtotime($periodeEnd));
		$start = explode('-',$start);
		$end = explode('-',$end);
		$startYear = $start[0];
		$startMonth = $start[1];
		$endYear = $end[0];
		$endMonth = $end[1];
		$data = array();
		
		//flag untuk perbuatahan tahun. jika tahun berubah patokan bulan tidak lagi $startMonth
		$sm=$se=$yearChange=0;
		for($year=$startYear; $year<=$endYear; $year++){	
			if ($startYear == $endYear){
				$sm = $startMonth;
				$se = $endMonth;
				$yearChange = false;
			}
			else{
				//jika tahun berubah, set bulan menjadi bulan pertama
				$sm = $yearChange? 1 : $startMonth;
				$se = 12;
				$yearChange = true;
			}
			$data = array_merge($data,  $this->_rata2SeluruhPoin($sm, $se, $year) );
		}
		
		return $data;
	}
	
	function _rata2SeluruhPoin($startMonth, $endMonth, $year){
		$data = array();
		for($month=$startMonth; $month<=$endMonth; $month++){
			//kita load semua data penilaian pada periode '$month-$year'
			$penilaianData = $this->db->get_where('penilaian',"year(point_tgl_aktif)='$year' AND month(point_tgl_aktif)='$month'");
			
			$devider = 0;	//jumlah kejadian
			$point = 0;		//total point
			foreach($penilaianData->result_array() as $row){
				$point += $row['point_point'];
				$devider++;				
			}
			
			if ($devider>0) $point = $point/$devider; 
			
			$data["$month-$year"] = $point;	
		}
		return $data;
	}

	/**
	 * Jumlah Reward dan Punishment dalam departemen
	 * F = (jumlah kejadian Reward)
	 * F = (jumlah kejadian Punishment)
	 * @param unknown_type $depID
	 * @param unknown_type $periodeStart
	 * @param unknown_type $periodeEnd
	 * @return array('PR'=> prestasi
	 * 				'PE'=> pelanggaran
	 * 		);
	 */
	function jumlahRewardPunishmentPerDepartemen($depID, $periodeStart, $periodeEnd){
		$start = date("Y-m",strtotime($periodeStart));
		$end   = date("Y-m",strtotime($periodeEnd));
		$start = explode('-',$start);
		$end = explode('-',$end);
		$startYear = $start[0];
		$startMonth = $start[1];
		$endYear = $end[0];
		$endMonth = $end[1];
		$data = array('PR'=>0,'PE'=>0);
		
		//flag untuk perbuatahan tahun. jika tahun berubah patokan bulan tidak lagi $startMonth
		$sm=$se=$yearChange=0;
		for($year=$startYear; $year<=$endYear; $year++){
			if ($startYear == $endYear){
				$sm = $startMonth;
				$se = $endMonth;
				$yearChange = false;
			}
			else{
				//jika tahun berubah, set bulan menjadi bulan pertama
				$sm = $yearChange? 1 : $startMonth;
				$se = 12;
				$yearChange = true;
			}
			
			$tmp = $this->_jumlahRewardPunishmentPerDepartemen($depID, $sm, $se, $year);
			$data['PR']+=$tmp['PR'];
			$data['PE']+=$tmp['PE'];
		}
		
		return $data;
	}
	
	function _jumlahRewardPunishmentPerDepartemen($depID, $startMonth, $endMonth, $year){
		//kumpulkan jabatan yang berada dalam departemen $departemenID
		$jbtData = $this->db->get_where('jabatan',"dep_id='$depID'");
		$jbtList = "";
		foreach ($jbtData->result_array() as $jbt){
			$jbtList .= ($jbtList? "," : "")."'$jbt[jbt_id]'";
		}
		if (!$jbtList)$jbtList="''";
		
		//kia cari dalam tabel karyawan_history_jabatan
		$karyJbtData = $this->db->get_where('karyawan_history_jabatan',"jbt_id IN ($jbtList)");
		$karyJbtList = "";
		foreach ($karyJbtData->result_array() as $karyJbt){
			$karyJbtList .= ($karyJbtList? "," : "")."'$karyJbt[kary_jbt_id]'";
		}
		if (!$karyJbtList)$karyJbtList="''";
		
		$data = array('PR'=>0,'PE'=>0); 	//jumlah prestasi & pelanggaran
		for($month=$startMonth; $month<=$endMonth; $month++){
			//kita sudah dapatkan id history jabatan karyawan,
			//cari dalam tabel penilaian yang memiliki id history jabatan tersebut
			$penilaianData = $this->db->get_where('penilaian',"kary_jbt_id IN ($karyJbtList) ".
				"AND year(point_tgl_aktif)='$year' AND month(point_tgl_aktif)='$month'");
			
			foreach($penilaianData->result_array() as $row){
				//load prestasi_pelanggaran_detil
				$this->db->join('prestasi_pelanggaran', 'prestasi_pelanggaran.achiev_id = prestasi_pelanggaran_detil.achiev_id');
				$detachievData = $this->db->get_where('prestasi_pelanggaran_detil',"detachiev_id='$row[detachiev_id]'");
				$detachievData = (array)$detachievData->row();
				
				$data[$detachievData['achiev_jenis']]++;
			}
			
		}
		return $data;
	}

	/**
	 * jumlah Pelanggaran yang terjadi pada seluruh departemen
	 * F += (jumlah kejadian pada departemen untuk seluruh departemen)
	 * @param $detachiev_id - jenis kejadian
	 * @param $periodeStart
	 * @param $periodeEnd
	 */
	function totalPelanggaranPerBidang($detachiev_id, $periodeStart, $periodeEnd){
		$start = date("Y-m",strtotime($periodeStart));
		$end   = date("Y-m",strtotime($periodeEnd));
		$start = explode('-',$start);
		$end = explode('-',$end);
		$startYear = $start[0];
		$startMonth = $start[1];
		$endYear = $end[0];
		$endMonth = $end[1];
		$data = 0;
		
		//flag untuk perbuatahan tahun. jika tahun berubah patokan bulan tidak lagi $startMonth
		$sm=$se=$yearChange=0;
		for($year=$startYear; $year<=$endYear; $year++){
			if ($startYear == $endYear){
				$sm = $startMonth;
				$se = $endMonth;
				$yearChange = false;
			}
			else{
				//jika tahun berubah, set bulan menjadi bulan pertama
				$sm = $yearChange? 1 : $startMonth;
				$se = 12;
				$yearChange = true;
			}
			
			$data += $this->_totalPelanggaranPerBidang($detachiev_id, $sm, $se, $year);
		}
		
		return $data;
	}
	
	function _totalPelanggaranPerBidang($detachiev_id, $startMonth, $endMonth, $year){
		$data = 0;
		for($month=$startMonth; $month<=$endMonth; $month++){
			//kita cari dalam tabel penilaian yang memilki kartegori kejadian $detachiev_id
			$penilaianData = $this->db->get_where('penilaian',"detachiev_id='$detachiev_id' ".
				"AND year(point_tgl_aktif)='$year' AND month(point_tgl_aktif)='$month'");
			
			//hitung jumlah kejadiaanya
			foreach($penilaianData->result_array() as $row){
				$data++;
			}
		}
		return $data;
	}

	/**
	 * jumlah prestasi dan pelanggaran tiap bidang
	 * F[prestasi] = jumlah kejadian prestasi / jumlah total
	 * F[pelanggaran] = jumlah kejadian pelanggaran / jumlah total
	 * @param $detachiev_id
	 * @param $periodeStart
	 * @param $periodeEnd
	 */
	function prestasiPelanggaranPerBidang($bidang, $periodeStart, $periodeEnd){
		$start = date("Y-m",strtotime($periodeStart));
		$end   = date("Y-m",strtotime($periodeEnd));
		$start = explode('-',$start);
		$end = explode('-',$end);
		$startYear = $start[0];
		$startMonth = $start[1];
		$endYear = $end[0];
		$endMonth = $end[1];
		$data = array('PR'=>0,'PE'=>0);
		
		//flag untuk perbuatahan tahun. jika tahun berubah patokan bulan tidak lagi $startMonth
		$sm=$se=$yearChange=0;
		for($year=$startYear; $year<=$endYear; $year++){
			if ($startYear == $endYear){
				$sm = $startMonth;
				$se = $endMonth;
				$yearChange = false;
			}
			else{
				//jika tahun berubah, set bulan menjadi bulan pertama
				$sm = $yearChange? 1 : $startMonth;
				$se = 12;
				$yearChange = true;
			}
			
			$tt = $this->_prestasiPelanggaranPerBidang($bidang, $sm, $se, $year);
			$data['PR'] = $tt['PR'];
			$data['PE'] = $tt['PE'];
		}
		
		return $data;
	}
	
	function _prestasiPelanggaranPerBidang($bidang, $startMonth, $endMonth, $year){
		//kita cari detachiev_id yang masuk dalam kelompok '$bidang'
		$this->db->join('prestasi_pelanggaran_detil',
			'prestasi_pelanggaran_detil.achiev_id=prestasi_pelanggaran.achiev_id');
		$detachievData = $this->db->get_where('prestasi_pelanggaran',"achiev_bidang='$bidang'");
		$detachievList = "";
		foreach($detachievData->result_array() as $row){
			$detachievList .= ($detachievList? ",":"")."'$row[detachiev_id]'";
		}
		if (!$detachievList) $detachievList="''";
		
		$count = 0; 						//jumlah kejadian
		$data = array('PR'=>0,'PE'=>0); 	//jumlah prestasi & pelanggaran
		for($month=$startMonth; $month<=$endMonth; $month++){
			//kita load penilaian, cari yang memliki 'detachiev_id' == $detachievList
			$penilaianData = $this->db->get_where('penilaian',"detachiev_id IN ($detachievList) ".
				"AND year(point_tgl_aktif)='$year' AND month(point_tgl_aktif)='$month'");
			
			foreach($penilaianData->result_array() as $row){
				//load prestasi_pelanggaran_detil, kita lihat jenisnya
				$this->db->join('prestasi_pelanggaran', 'prestasi_pelanggaran.achiev_id = prestasi_pelanggaran_detil.achiev_id');
				$detachievData = $this->db->get_where('prestasi_pelanggaran_detil',"detachiev_id='$row[detachiev_id]'");
				$detachievData = (array)$detachievData->row();
				
				$data[$detachievData['achiev_jenis']]++;
				$count++;
			}
		}
		
		if ($count){
			$data['PR'] = $data['PR']/$count;
			$data['PE'] = $data['PE']/$count;
		}
		return $data;
	}

	function rata2RewardPunishment($periodeStart, $periodeEnd){
		$start = date("Y-m",strtotime($periodeStart));
		$end   = date("Y-m",strtotime($periodeEnd));
		$start = explode('-',$start);
		$end = explode('-',$end);
		$startYear = $start[0];
		$startMonth = $start[1];
		$endYear = $end[0];
		$endMonth = $end[1];
		$data = array();
		
		//flag untuk perbuatahan tahun. jika tahun berubah patokan bulan tidak lagi $startMonth
		$sm=$se=$yearChange=0;
		for($year=$startYear; $year<=$endYear; $year++){
			if ($startYear == $endYear){
				$sm = $startMonth;
				$se = $endMonth;
				$yearChange = false;
			}
			else{
				//jika tahun berubah, set bulan menjadi bulan pertama
				$sm = $yearChange? 1 : $startMonth;
				$se = 12;
				$yearChange = true;
			}
			
			$data = array_merge($data, $this->_rata2RewardPunishment($sm, $se, $year));
		}
		
		return $data;
	}
	
	function _rata2RewardPunishment($startMonth, $endMonth, $year){
		$count = 0; 						//jumlah kejadian
		$data = array(); 	//jumlah prestasi & pelanggaran
		for($month=$startMonth; $month<=$endMonth; $month++){
			//kita load penilaian, cari yang memliki 'detachiev_id' == $detachievList
			$penilaianData = $this->db->get_where('penilaian',"year(point_tgl_aktif)='$year' AND month(point_tgl_aktif)='$month'");
			
			$data["$month-$year"]=array('PR'=>0, 'PE'=>0);
			foreach($penilaianData->result_array() as $row){
				//load prestasi_pelanggaran_detil, kita lihat jenisnya
				$this->db->join('prestasi_pelanggaran', 'prestasi_pelanggaran.achiev_id = prestasi_pelanggaran_detil.achiev_id');
				$detachievData = $this->db->get_where('prestasi_pelanggaran_detil',"detachiev_id='$row[detachiev_id]'");
				$detachievData = (array)$detachievData->row();
				
				$data["$month-$year"][$detachievData['achiev_jenis']]++;
				$count++;
			}
			
			//normalisasi data
			if (empty($data["$month-$year"]))		$data["$month-$year"] = array('PE'=>0,'PR'=>0);
			if (!is_array($data["$month-$year"]))	$data["$month-$year"] = array('PE'=>0,'PR'=>0);
		}
		
		if ($count){
			foreach($data as $key=>$value){
				foreach($value as $kk=>$vv){
					$data[$key][$kk] = $vv/$count; 
				}
			}
		}
		return $data;
	}
}